"""
-- coding: utf-8 --
@Time : 2023/8/1
@Author : 张书译
@Describe:读取MySQL方法
"""

import pymysql

from Comm.unit.Logger import logger
from Comm.utils.ReadIniFile import ReadIniFile
import pandas as pd
from sqlalchemy import create_engine


class ConnectMysql:
    """
    inset into数据库
    :return:
    """

    hi = ReadIniFile()

    def __init__(self,
                 host=hi.get_str("mysql", "host"),
                 user=hi.get_str("mysql", "user"),
                 password=hi.get_str("mysql", "password"),
                 db=hi.get_str("mysql", "database"),
                 port=hi.get_str("mysql", "port"),
                 charset='utf8',
                 autocommit=True
                 ):
        self.host = host
        self.user = user
        self.password = password
        self.db = db
        self.port = port
        self.charset = charset
        self.autocommit = autocommit

    def pdLink(self):
        # PD连接方法
        con = f"mysql+pymysql://{self.user}:{self.password}@{self.host}:{self.port}/{self.db}"
        engine = create_engine(con)
        return engine

    def myLink(self):
        # pymysql连接方法
        conn = pymysql.connect(
            host=self.host,
            user=self.user,
            password=self.password,
            db=self.db,
            port=int(self.port),
            charset=self.charset,
            autocommit=self.autocommit,  # 如果插入数据，， 是否自动提交? 和conn.commit()功能一致。
        )
        return conn

    def PMSelect(self, insert_sql, conn=None, look=None):
        """查询"""
        if conn is None:
            conn = self.myLink()
        try:
            if look:
                pd.set_option('display.max_columns', None, 'display.width', None)
                pd.set_option('display.max_rows', None, 'display.width', None)
            results = pd.read_sql_query(insert_sql, conn)
            return results
        except Exception as e:
            logger.error(f"数据操作失败：{e}")

    def PMUpdate(self, insert_sql, conn=None):
        if conn is None:
            conn = self.myLink()
        cur = conn.cursor()
        """修改"""
        try:
            cur.execute(insert_sql)
        except Exception as e:
            logger.error(f"修改数据失败：{e}")
        else:
            conn.commit()
            logger.info("修改数据成功;")

    def PDSelect(self, sql, engine=None, look=None):
        if engine is None:
            engine = self.pdLink()
        try:
            if look:
                pd.set_option('display.max_columns', None, 'display.width', None)
                pd.set_option('display.max_rows', None, 'display.width', None)
            df = pd.read_sql_query(sql, engine)
            logger.info(f'操作成功：{df}')
            return df
        except Exception as e:
            logger.error(f"数据操作失败：{e}")

    def PDUpdate(self, sql, engine=None):
        if engine is None:
            engine = self.pdLink()
        try:
            engine.execute(sql)
            logger.info(f'操作成功：{sql}')
        except Exception as e:
            logger.error(f"修改数据失败：{e}")
